#!/usr/bin/python
# -*- coding: utf-8 -*-
# Description:
from flask import current_app
from app import db

class ExampleModel(db.Model):
    __bind_key__ = 'db_zj2'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
class SQLAlchemyDao:
    """封装 Flask_SQLAlchemy 数据库操作基础类"""

    @staticmethod
    def select_one(sql, params=None):
        """
        查询，返回单条记录
        :sql: 单条sql语句
        :params: 参数 字典格式

        :return: 结果字典 {k:v,...}
        """
        if params is None:
            params = {}
        try:
            resultProxy = db.session.execute(sql, params)
            result_tuple = resultProxy.fetchone()
            result = {}
            if result_tuple:
                result = dict(zip(resultProxy.keys(), list(result_tuple)))
            return result
        except Exception:
            db.session.rollback()
            raise """Exception related to operation with DB2."""

    @staticmethod
    def select_all(sql, params=None):
        """
        查询
        :sql: 单条sql语句
        :params: 参数 字典格式

        :return: 列表   [{k:v,...},...]
        """
        if params is None:
            params = {}
        try:
            resultProxy = db.session.execute(sql, params)
            result_tuple_list = resultProxy.fetchall()
            result = []
            if result_tuple_list:
                for row in result_tuple_list:
                    result_row = dict(zip(resultProxy.keys(), row))
                    result.append(result_row)
            return result
        except Exception:
            db.session.rollback()
        #     raise """Exception related to operation with DB2."""

    @staticmethod
    def select_all_tg_RDS(sql, params=None):
        """
        查询
        :sql: 单条sql语句
        :params: 参数 字典格式

        :return: 列表   [{k:v,...},...]
        """
        if params is None:
            params = {}
        # try:
        resultProxy = db.session.execute(sql, bind=db.get_engine(current_app, 'db_tg_RDS'))
        result_tuple_list = resultProxy.fetchall()
        result = []
        if result_tuple_list:
            for row in result_tuple_list:
                result_row = dict(zip(resultProxy.keys(), row))
                result.append(result_row)
        return result
        # except Exception:
        #     db2.session.rollback()
        #     raise """Exception related to operation with DB2."""

    @staticmethod
    def execute(sql, params=None):
        """
        执行单条语句 (update, insert)
        :sql: 单条sql语句，注意SQL查询如法格式
        :params: 参数 支持 {k:v,...}（单行插入） or [{k:v,...},...]（多行插入）
        """
        if params is None:
            params = {}
        try:
            db.session.execute(sql, params)
            db.session.commit()
        except Exception:
            db.session.rollback()
            raise """Exception related to operation with DB2."""

    @staticmethod
    def execute_many(sql_list):
        """
        执行多条语句，失败自动回滚
        :sql_list: sql语句列表 [{sql,params},...]
        """
        if not isinstance(sql_list, (list, tuple)):
            current_app.logger.info('type of the sql_list must be list or tuple')
        if len(sql_list) == 0:
            current_app.logger.info("sql_list's length can't be 0")
        for statement in sql_list:
            if not isinstance(statement, dict):
                current_app.logger.info("sql_list's format error")
        try:
            for s in sql_list:
                db.session.execute(s.get('sql'), s.get('params'))
            db.session.commit()
        except Exception:
            db.session.rollback()
            raise """Exception related to operation with DB2."""
